As the amount of data available on personal computers increases, the need for rich, fast and powerful analysis tools has become critical. A standard analysis tool currently available and commonly used is a spreadsheet. The classical spreadsheet is a good tool to do formulaic analysis of fixed size data. However, when the data is of varying size, or if cross-tabulation based analysis is needed, the classical spreadsheet is inadequate.
Current spreadsheets typically include the ability to cross-tabulate or pivot rows or columns of a report. A spreadsheet add-on tool entitled DataPivot.RTM. was the pioneer of this capability to restructure data and reports. DataPivot.RTM. is a registered trademark of Brio Technology, Inc. of 444 Castro Street, Suite 700, Mountain View, Calif. Other major spreadsheet programs have also now added this ability to cross-tabulate or pivot data. The DataPivot Reference and User's Guide published by Brio Technology, Inc. of 444 Castro Street, Suite 700, Mountain View, Calif. is attached hereto as Appendix C.
Cross-tabulation analysis allows a user to dynamically restructure data. Consider as an example the rows of source data contained in Table 1:
TABLE 1 ______________________________________ Example Data For Reports Year Quarter Winery Grape Cases Sold ______________________________________ 1991 Q1 Beaulieu Cabernet Sauvignon 450 1991 Q2 Beaulieu Cabernet Sauvignon 550 1991 Q3 Beaulieu Cabernet Sauvignon 575 1991 Q4 Beaulieu Cabernet Sauvignon 650 1991 Q1 Beaulieu Cabernet Sauvignon 320 1991 Q2 Beaulieu Cabernet Sauvignon 325 1991 Q3 Beaulieu Cabernet Sauvignon 330 1991 Q4 Beaulieu Cabernet Sauvignon 350 1991 Q1 Beaulieu Cabernet Sauvignon 350 1991 Q2 Beaulieu Cabernet Sauvignon 360 1991 Q3 Beaulieu Cabernet Sauvignon 370 1991 Q4 Beaulieu Cabernet Sauvignon 375 1991 Q1 Beaulieu Cabernet Sauvignon 230 1991 Q2 Beaulieu Cabernet Sauvignon 235 1991 Q3 Beaulieu Cabernet Sauvignon 240 1991 Q4 Beaulieu Cabernet Sauvignon 260 1992 Q2 Beaulieu Cabernet Sauvignon 625 1992 Q4 Beaulieu Cabernet Sauvignon 670 1992 Q1 Beaulieu Cabernet Sauvignon 310 1992 Q2 Beaulieu Cabernet Sauvignon 314 1992 Q3 Beaulieu Cabernet Sauvignon 324 1992 Q4 Beaulieu Cabernet Sauvignon 388 1992 Q1 Beaulieu Cabernet Sauvignon 620 1992 Q1 Beaulieu Cabernet Sauvignon 400 1992 Q2 Beaulieu Cabernet Sauvignon 411 1992 Q3 Beaulieu Cabernet Sauvignon 419 1992 Q4 Beaulieu Cabernet Sauvignon 423 1992 Q1 Beaulieu Cabernet Sauvignon 200 1992 Q2 Beaulieu Cabernet Sauvignon 213 1992 Q3 Beaulieu Cabernet Sauvignon 223 1992 Q3 Beaulieu Cabernet Sauvignon 650 1992 Q4 Beaulieu Cabernet Sauvignon 233 1991 Q2 Beaulieu Chardonnay 425 1991 Q3 Beaulieu Chardonnay 460 1991 Q4 Beaulieu Chardonnay 450 1991 Q1 Beaulieu Chardonnay 230 1991 Q2 Beaulieu Chardonnay 235 1991 Q3 Beaulieu Chardonnay 240 1991 Q4 Beaulieu Chardonnay 255 1991 Q1 Beaulieu Chardonnay 180 1991 Q2 Beaulieu Chardonnay 200 1991 Q3 Beaulieu Chardonnay 225 1991 Q4 Beaulieu Chardonnay 230 1991 Q1 Beaulieu Chardonnay 270 1991 Q2 Beaulieu Chardonnay 270 1991 Q3 Beaulieu Chardonnay 275 1991 Q4 Beaulieu Chardonnay 290 1991 Q1 Beaulieu Chardonnay 450 1992 Q1 Beaulieu Chardonnay 400 1992 Q2 Beaulieu Chardonnay 411 1992 Q3 Beaulieu Chardonnay 415 1992 Q4 Beaulieu Chardonnay 419 1992 Q1 Beaulieu Chardonnay 200 1992 Q2 Beaulieu Chardonnay 201 1992 Q3 Beaulieu Chardonnay 208 1992 Q4 Beaulieu Chardonnay 220 1992 Q1 Beaulieu Chardonnay 170 1992 Q2 Beaulieu Chardonnay 175 1992 Q3 Beaulieu Chardonnay 180 1992 Q4 Beaulieu Chardonnay 175 1992 Q1 Beaulieu Chardonnay 230 1992 Q2 Beaulieu Chardonnay 232 1992 Q3 Beaulieu Chardonnay 219 1992 Q4 Beaulieu Chardonnay 225 1991 Q1 Beaulieu Merlot 180 1991 Q2 Duckhorn Merlot 660 1991 Q3 Duckhorn Merlot 665 1991 Q4 Duckhorn Merlot 670 1991 Q1 Duckhorn Merlot 325 1991 Q2 Duckhorn Merlot 329 1991 Q3 Duckhorn Merlot 330 1991 Q4 Duckhorn Merlot 341 1991 Q1 Duckhorn Merlot 660 1991 Q3 Beaulieu Merlot 350 1991 Q2 Beaulieu Merlot 325 1991 Q1 Beaulieu Merlot 320 1991 Q4 Beaulieu Merlot 650 1991 Q3 Beaulieu Merlot 625 1991 Q2 Beaulieu Merlot 575 1991 Q1 Beaulieu Merlot 550 1991 Q4 Beaulieu Merlot 280 1991 Q3 Beaulieu Merlot 230 1991 Q2 Beaulieu Merlot 275 1991 Q1 Beaulieu Merlot 270 1991 Q4 Beaulieu Merlot 200 1991 Q3 Beaulieu Merlot 195 1991 Q2 Beaulieu Merlot 190 1991 Q2 Duckhorn Merlot 481 1991 Q3 Duckhorn Merlot 485 1991 Q4 Duckhorn Merlot 499 1991 Q1 Duckhorn Merlot 275 1991 Q2 Duckhorn Merlot 280 1991 Q3 Duckhorn Merlot 285 1991 Q4 Duckhorn Merlot 289 1991 Q1 Duckhorn Merlot 200 1991 Q2 Duckhorn Merlot 206 1991 Q3 Duckhorn Merlot 212 1991 Q4 Duckhorn Merlot 220 1991 Q1 Duckhorn Merlot 289 1991 Q2 Duckhorn Merlot 287 1991 Q3 Duckhorn Merlot 291 1991 Q4 Duckhorn Merlot 301 1991 Q1 Duckhorn Merlot 480 1991 Q4 Beaulieu Merlot 370 1992 Q2 Duckhorn Merlot 691 1992 Q1 Beaulieu Merlot 620 1992 Q4 Beaulieu Merlot 244 1992 Q3 Beaulieu Merlot 234 1992 Q2 Beaulieu Merlot 232 1992 Q1 Beaulieu Merlot 230 1992 Q4 Beaulieu Merlot 181 1992 Q3 Beaulieu Merlot 178 1992 Q2 Beaulieu Merlot 171 1992 Q1 Beaulieu Merlot 170 1992 Q2 Beaulieu Merlot 621 1992 Q3 Beaulieu Merlot 655 1992 Q4 Beaulieu Merlot 645 1992 Q1 Beaulieu Merlot 310 1992 Q2 Beaulieu Merlot 321 1992 Q3 Beaulieu Merlot 315 1992 Q4 Beaulieu Merlot 330 1992 Q1 Duckhorn Merlot 690 1992 Q4 Duckhorn Merlot 280 1992 Q3 Duckhorn Merlot 275 1992 Q2 Duckhorn Merlot 273 1992 Q1 Duckhorn Merlot 270 1992 Q4 Duckhorn Merlot 198 1992 Q3 Duckhorn Merlot 195 1992 Q2 Duckhorn Merlot 191 1992 Q1 Duckhorn Merlot 190 1992 Q4 Duckhorn Merlot 301 1992 Q3 Duckhorn Merlot 295 1992 Q2 Duckhorn Merlot 291 1992 Q1 Duckhorn Merlot 290 1992 Q4 Duckhorn Merlot 510 1992 Q3 Duckhorn Merlot 508 1992 Q2 Duckhorn Merlot 502 1992 Q1 Duckhorn Merlot 500 1992 Q4 Duckhorn Merlot 360 1992 Q3 Duckhorn Merlot 344 1992 Q2 Duckhorn Merlot 345 1992 Q1 Duckhorn Merlot 340 1992 Q4 Duckhorn Merlot 699 1992 Q3 Duckhorn Merlot 695 ______________________________________
The data included in Table 1 may be condensed into the following easy to read cross-tabulated report having the top labels of year and quarter, the side labels of winery and grape and the facts of cases sold. The facts represent the aggregated values in the center of the report displayed as the number of cases of each grape sold in each quarter for each winery.
__________________________________________________________________________ Report 1 1991 1992 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 __________________________________________________________________________ Beaulieu Cabernet Sauvignon Cases Sold 1350 1470 1515 1635 1530 1563 1616 1714 Chardonnay Cases Sold 1130 1130 1200 1225 1000 1019 1022 1039 Merlot Cases Sold 1320 1365 1400 1500 1330 1345 1382 1400 Duckhorn Merlot Cases Sold 2229 2243 2268 2320 2280 2293 2312 2348 __________________________________________________________________________
This same data may also be condensed into the following different cross-tabulated report having the top label of quarter, the side labels of year, winery and grape and the facts of cases sold. The facts still represent the aggregated values in the center of the report displayed as the number of cases each grape sold in each quarter for each winery.
__________________________________________________________________________ Report 2 Q1 Q2 Q3 Q4 __________________________________________________________________________ 1991 Beaulieu Cabernet Sauvignon Cases Sold 1350 1470 1515 1635 Chardonnay Cases Sold 1130 1130 1200 1225 Merlot Cases Sold 1320 1365 1400 1500 Duckhorn Merlot Cases Sold 2229 2243 2266 2320 1992 Beaulieu Cabernet Sauvignon Cases Sold 1530 1563 1616 1714 Chardonnay Cases Sold 1000 1019 1022 1039 Merlot Cases Sold 1330 1345 1382 1400 Duckhorn Merlot Cases Sold 2290 2293 2312 2348 __________________________________________________________________________
The only difference between the Report 1 and the Report 2 is the manner in which the data or facts are displayed. The year label was pivoted or rotated from the top of the report, as illustrated in the Report 1, to the side of the report, as illustrated in the Report 2. With a good cross-tabulation tool a user should be able to go between the two reports quickly and easily.
Current cross-tabulation tools will become overloaded if a user attempts to push too much source data through the cross-tabulation engine. Such prior art cross-tabulation engines will become overloaded when users work on 10,000 to 20,000 rows of source data, because these engines rely on access to the rows of source data in order to pivot or rearrange the data for display in a cross-tabulation report. Users who desire to work with this amount of source data are prevented from using the prior art cross-tabulation tools.
The main reason why the cross-tabulation tools of the prior art are unable to manipulate large amounts of data is attributed to the storage structures and the methods employed. While the exact methods are not publicly available, it is believed that the cross-tabulation tools of the prior art are not self-contained, but require complete access to the source data. It is also believed that the cross-tabulation data structures used by the tools of the prior art use either a cached display template or a hashed data storage module. Neither of these data structures provides enough structural information in the cross-tabulation data structure to support arbitrary restructurings without access to the source data.
What is needed is a cross-tabulation tool which pivots and restructures a cross-tabulation report easily and efficiently without requiring access to the source data. What is also needed is a cross-tabulation tool which will accumulate an incoming stream of data records into a data structure which may then be used to form and restructure a cross-tabulation report without the necessity of storing the stream of data records into a computer's memory for later access by the cross-tabulation tool.